package service;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;


import pojo.DishesReview;
import pojo.MemberLogin;
import pojo.dishes;
import pojo.review;


import base.BaseService;
public class ReviewService extends  BaseService{
	 public class ReviewRowMapper implements RowMapper<review> {
			
  	   public review mapRow(ResultSet rs, int rownum) throws SQLException {
  
	review inf = new review();
	inf.setId(rs.getInt("id"));
	inf.setUserid(rs.getInt("userid"));
	inf.setOrderid(rs.getInt("orderid"));
	inf.setTime(rs.getDate("time"));
	inf.setLevel(rs.getInt("level"));
	inf.setContent(rs.getString("content"));

	

	return inf;
			}
     }
	 //评论数量
	 public int getALL(String where){
			String sql = "SELECT count(*) FROM review "+where+"";
			
			int count1 = this.jdbcTemplate.queryForInt(sql);
			
			return count1;
			
		}
	 //评论列表
		public List<review> getReview(int pageno,int pagesize,String where){
				String sql = "SELECT * from review "+where+" limit "+(pageno-1)*pagesize+","+pagesize;
				return jdbcTemplate.query(sql,new ReviewRowMapper());
		}
		//删除评论

		 public int DeleteR(int id){
				
				String sql ="delete from review where id=?";
				
				return jdbcTemplate.update(sql,id);
				
		 }
		 //根据orderid获取评论信息
		 public review getorderId(int orderid){
				
				review inf=null;
				String sql="select * from review where orderid=?";
				try{
				inf=this.jdbcTemplate.queryForObject(sql, new ReviewRowMapper(),orderid);
				}catch(EmptyResultDataAccessException e){
					
				}
				return inf;
			}
		 //添加（总体）评论
		 public int add(int userid,int orderid,int level,String content ){
				Timestamp time=new Timestamp(System.currentTimeMillis());
				
				String sql ="insert into review(userid,orderid,time,level,content) values(?,?,?,?,?)";
				
				return jdbcTemplate.update(sql,userid,orderid,time,level,content);
			}
		 //菜品评论列表
			public List<DishesReview> getDishesReview(int dishesid){
				class DishesReviewRowMapper implements org.springframework.jdbc.core.RowMapper<DishesReview> {
					 public DishesReview mapRow(ResultSet rs, int rownum) throws SQLException {

						 DishesReview inf = new DishesReview();
							inf.setId(rs.getInt("id"));
							inf.setUserid(rs.getInt("userid"));
							inf.setDishesid(rs.getInt("dishesid")); 
							inf.setColor(rs.getInt("color"));
							inf.setSmell(rs.getInt("smell"));
							inf.setTaste(rs.getInt("taste"));
							inf.setContent(rs.getString("content"));
							inf.setTime(rs.getTimestamp("time"));				
							return inf;
									}
							}	
					String sql = "SELECT * from dishesReview where dishesid="+dishesid;
					return jdbcTemplate.query(sql,new DishesReviewRowMapper());
			}
			//添加菜品评论
			 public int addDishesReview(int userid,int dishesid,int color,int smell,int taste,String content ){
					Timestamp time=new Timestamp(System.currentTimeMillis());
					
					String sql ="insert into dishesReview(time,userid,dishesid,color,smell,taste,content) values(?,?,?,?,?,?,?)";
					
					return jdbcTemplate.update(sql,time,userid,dishesid,color,smell,taste,content);
				}
			 //查询该用户是否订过此菜品
			 
		     public int getnum(int userid,String dishesname){
		    	 
		    	 String sql="select count(*) from `order` where memberId=? and id in (select orderid from orderdetail where dishesName=?) and tradeStatus=4";
		    	 
		    	 return this.jdbcTemplate.queryForInt(sql,userid,dishesname);
		     }
		    //查询用户是否评论过此菜品
		     
		     public int getreviewnum(int userid,int dishesid){
		    	 String sql="select count(*) from dishesReview where userid="+userid+" and dishesid="+dishesid;
		    	 
		    	 return this.jdbcTemplate.queryForInt(sql);
		    		 
		     }

		 	public List<DishesReview> getDishesReview(String where){
		 		class DishesReviewRowMapper implements org.springframework.jdbc.core.RowMapper<DishesReview> {
					 public DishesReview mapRow(ResultSet rs, int rownum) throws SQLException {

						 DishesReview inf = new DishesReview();
							inf.setId(rs.getInt("id"));
							inf.setUserid(rs.getInt("userid"));
							inf.setDishesid(rs.getInt("dishesid")); 
							inf.setColor(rs.getInt("color"));
							inf.setSmell(rs.getInt("smell"));
							inf.setTaste(rs.getInt("taste"));
							inf.setContent(rs.getString("content"));
							inf.setTime(rs.getTimestamp("time"));				
							return inf;
									}
							}	
				String sql = "SELECT * from dishesreview "+where;
				return jdbcTemplate.query(sql,new DishesReviewRowMapper());
		}
}

